﻿using NLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Fishing.Model;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace Fishing.Dal
{
    public class AdminUserRoleDal
    {
        private static readonly Logger _logger = LogManager.GetCurrentClassLogger();


        /// <summary>
        /// ConStr
        /// </summary>
        private static readonly string ConStr = ConfigurationManager.ConnectionStrings["_fishingdb"].ConnectionString;

        public bool InsertUserDate(admin_user role)
        {

            var ret = false;

            const string sql = "insert into admin_user (userID,userName,password,`status`,createTime,createBy) values " +
                               "(@userID,@userName,@password,@status,now(),@createBy)";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@userID", MySqlDbType.VarChar, 10).Value = role.userID;
            command.Parameters.Add("@userName", MySqlDbType.VarChar, 10).Value = role.userName;
            command.Parameters.Add("@password", MySqlDbType.VarChar, 20).Value = role.password;
            command.Parameters.Add("@status", MySqlDbType.VarChar, 20).Value = role.status;
            command.Parameters.Add("@createBy", MySqlDbType.VarChar, 10).Value = role.createBy;
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("InsertDate admin_user  ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }
            return ret;
        }

        public bool InsertUserRoleDate(admin_user_role role)
        {

            var ret = false;

            const string sql = "insert into admin_user_role  values " +
                               "(@userRoleID,@userID,@roleID)";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@userRoleID", MySqlDbType.VarChar, 10).Value = role.userRoleID;
            command.Parameters.Add("@userID", MySqlDbType.VarChar, 10).Value = role.userID;
            command.Parameters.Add("@roleID", MySqlDbType.VarChar, 10).Value = role.roleID;
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("InsertDate admin_user_role  ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }
            return ret;
        }

        public bool UpdateUserDate(admin_user role)
        {

            var ret = false;

            const string sql = "UPDATE admin_user set userName=@userName , `password`=@password,`status`=@status, updateTime=now(),updateBy=@updateBy where userID=@userID";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@userID", MySqlDbType.VarChar, 10).Value = role.userID;
            command.Parameters.Add("@userName", MySqlDbType.VarChar, 10).Value = role.userName;
            command.Parameters.Add("@password", MySqlDbType.VarChar, 20).Value = role.password;
            command.Parameters.Add("@status", MySqlDbType.VarChar, 20).Value = role.status;
            command.Parameters.Add("@updateBy", MySqlDbType.VarChar, 10).Value = "";
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("UpdateDate admin_user ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }
            return ret;
        }

        public bool UpdateUserRoleDate(admin_user_role role)
        {

            var ret = false;

            const string sql = "UPDATE admin_user_role set roleID=@roleID where userID=@userID";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@userID", MySqlDbType.VarChar, 10).Value = role.userID;
            command.Parameters.Add("@roleID", MySqlDbType.VarChar, 10).Value = role.roleID;
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("UpdateDate admin_user_role ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }
            return ret;
        }

        public bool DelUserDate(string userID)
        {

            var ret = false;
            const string sql =
                "delete FROM admin_user where userID=@userID";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@userID", MySqlDbType.VarChar, 10).Value = userID;
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("DelDate admin_user ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }


            return ret;
        }

        public bool DelUserRoleDate(string userID)
        {

            var ret = false;
            const string sql =
                "delete FROM admin_user_role where userID=@userID";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@userID", MySqlDbType.VarChar, 10).Value = userID;
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("DelDate admin_user ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }


            return ret;
        }


        public List<admin_user> GetUserDataByRoleID(string roleID,string where)
        {
            var retList = new List<admin_user>();
            var sql = "select * from  admin_user where userID in (select userID from admin_user_role where roleID=@roleID ) ";
            if (where.Length > 0)
            {
                sql = sql + where;
            }

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@roleID", MySqlDbType.VarChar, 10).Value = roleID;
            try
            {
                connection.Open();
                using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            retList.Add(new admin_user()
                            {
                                userID = reader["userID"].ToString(),
                                userName = reader["userName"].ToString(),
                                password = reader["password"].ToString(),
                                status = reader["status"].ToString()
                            });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("GetRoleDataBy ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();
            }
            return retList;

        }

        public List<admin_user> GetRoleDataBy(string where)
        {
            var retList = new List<admin_user>();
            var sql = "select * from admin_user where 1=1 ";
            if (where.Length > 0)
            {
                sql = sql + where;
            }

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            try
            {
                connection.Open();
                using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            retList.Add(new admin_user()
                            {
                                userID = reader["userID"].ToString(),
                                userName = reader["userName"].ToString(),
                                password = reader["password"].ToString(),
                                status = reader["status"].ToString()
                            });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("GetRoleDataBy ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();
            }
            return retList;

        }

        public bool ResetPassword(string  userID)
        {

            var ret = false;

            const string sql = "UPDATE admin_user set `password`='123456' where userID=@userID";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@userID", MySqlDbType.VarChar, 10).Value = userID;
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("ResetPassword ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }
            return ret;
        }


        public bool IsUserID(string userID)
        {
            var ret = true;
            const string sql = "select userID FROM admin_user   where  userID=@userID ";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@userID", MySqlDbType.VarChar, 10).Value = userID;
            try
            {
                connection.Open();
                if (command.ExecuteScalar() != null)
                {
                    ret = false;
                }
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("IsRoleByUser ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }

            return ret;
        }

    }
}